﻿using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace WebReport.Controllers
{
    public class TestController : Controller
    {
        public class china_area
        {

            public int id { get; set; }
            public int parent_id { get; set; }
            public string code { get; set; }
            public string name { get; set; }
            public string created_at { get; set; }
            public string updated_at { get; set; }
        }
        public IActionResult Index()
        {
            DataSet dataSet = new DataSet();
            var stus = ExecuteNonDataSet("select name,id from china_area  where parent_id=0", out dataSet); //获取总页数

            ViewData["Province"] = dataSet.Tables[0].AsEnumerable().Select(a => new SelectListItem() { Text = a.Field<string>("name"), Value = a.Field<int>("id").ToString() }).ToList();
            //第一次加载页面初始化数据
            return View();//返回视图
        }
        public ActionResult GetCity(int precityid)
        {

            DataSet dataSet = new DataSet();
            var stus = ExecuteNonDataSet($@"select name,id from china_area  where parent_id={precityid}", out dataSet); //获取总页数
            var citys = dataSet.Tables[0].AsEnumerable().ToList().Select(a => new { cityname = a.Field<string>("name"), cityid = a.Field<int>("id") });
            return Json(citys);//有数据，根据查询的数据序列化生成一条新数据，返回json数据
        }


        string _connString = @"Server=DESKTOP-9GRFFRR;database=Test_Demo;Trusted_Connection=True;MultipleActiveResultSets=True;";
        //@"Server=localhost\SQLEXPRESS;database=Test_Demo;Trusted_Connection=True;MultipleActiveResultSets=True;";
        //"Server=192.168.0.210;Database=Test_Demo;User=iROCK;Password=apatuser123;MultipleActiveResultSets=True;";
        public bool OpenDataBase()
        {
            try
            {
                //创建数据库连接对象
                using (SqlConnection sqlConn = new SqlConnection(_connString))
                {
                    //打开连接
                    sqlConn.Open();
                    sqlConn.Close();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        ///<summary>
        ///定义函数根据传入的参数，执行SQL语句获取影响的行数
        ///</summary>
        public bool ExecuteNonQuery(string sql, out int iResult)
        {
            iResult = 0;
            //创建连接数据库对象
            using (SqlConnection sqlConn = new SqlConnection(_connString))
            {
                //打开连接
                sqlConn.Open();
                //创建执行SQL语句对象
                using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
                {
                    iResult = sqlCmd.ExecuteNonQuery();
                    sqlConn.Close();
                    sqlCmd.Dispose();
                    return true;
                }
            }
        }

        ///<summary>
        ///定义函数根据传入的参数，执行SQL语句获取数据集DataSet对象
        ///</summary>
        public bool ExecuteNonDataSet(string sql, out DataSet dataSet)
        {
            dataSet = null;
            //创建连接数据库对象
            using (SqlConnection sqlConn = new SqlConnection(_connString))
            {
                //创建执行SQL语句对象
                using (SqlDataAdapter sqlData = new SqlDataAdapter(sql, sqlConn))
                {
                    //打开连接
                    sqlConn.Open();
                    dataSet = new DataSet();
                    sqlData.Fill(dataSet, "ds");
                    sqlConn.Close();
                    sqlData.Dispose();
                    return true;
                }
            }

        }

    }
}
